﻿using Common;
using DBUtility;
using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BaseDal
{
    public class AssetUpkeepDao
    {
        // 使用 Lazy<T> 实现单例模式，确保实例的惰性初始化和线程安全。
        private static readonly Lazy<AssetUpkeepDao> _instance = new Lazy<AssetUpkeepDao>(() => new AssetUpkeepDao());

        /// <summary>
        /// 获取类的单例实例。
        /// </summary>
        public static AssetUpkeepDao Instance { get; } = _instance.Value;

        // 私有构造函数，防止外部实例化该类。
        private AssetUpkeepDao()
        { }

        private const string tableH = "AssetUpkeepHead";

        private const string tableB = "AssetUpkeepBody";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + tableH);
            strSql.Append(" where IsDelete=0 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT t1.ID,t1.AU_Number,t1.AU_Date,t2.AU_AssetNo,t2.AU_AssetName,t1.AU_Person,t1.AU_Spend,t1.AU_Content,t1.AU_Status ");
            sb.Append($"FROM {tableH} t1 Left Join {tableB} t2 ON t1.ID=t2.HeadID and t2.IsDelete=0 ");
            sb.Append(" Where t1.IsDelete=0 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询子表SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectBodySql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,HeadID,AU_AssetNo,AU_AssetName,AU_Specs,AU_Unit,AU_Price,AU_DeptId,AU_DeptName,AU_Location,StatusTag ");
            sb.Append("FROM " + tableB);
            sb.Append(" Where IsDelete=0 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AssetUpkeepHead GetModelById(int id)
        {
            // 查询主表
            var sb = new StringBuilder();
            sb.Append("SELECT ID,AU_Number,AU_Date,AU_Person,AU_Spend,AU_Content,AU_Status ");
            sb.Append("FROM " + tableH);
            sb.Append(" Where IsDelete=0 and ID=@id");

            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                AssetUpkeepHead model = CommonOperation.DataTableToModel<AssetUpkeepHead>(ds.Tables[0]);
                // 查询子表
                model.ItemList = GetBodyList(model.ID);

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询子表
        /// </summary>
        /// <param name="headID"></param>
        /// <returns></returns>
        public List<AssetUpkeepBody> GetBodyList(decimal headID)
        {
            var sb = GetSelectBodySql("and HeadID=@headID");
            SqlParameter[] param1 = { new SqlParameter("@headID", headID) };
            DataSet ds = SqlHelper.Query(sb.ToString(), param1);
            return CommonOperation.ConvertDataTableToModelList<AssetUpkeepBody>(ds.Tables[0]);
        }

        /// <summary>
        /// 查询维护资产（新增、修改用）
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetKeepAsset(string strWhere)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT t1.ID,t1.AssetNo,t1.AssetName,t1.Specs,t1.CreateTime,t2.Unit,t2.Price,t2.DeptName,t2.Location,t2.AssetStatus ");
            sb.Append("FROM AssetKeepWarn t1 Left Join AssetHead t2 On t1.AssetNo = t2.AssetNo and t2.IsDelete=0");
            sb.Append(" Where t1.IsDelete=0 and t1.Status in(3,4) ");
            sb.Append(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return ds.Tables[0];
        }

        /// <summary>
        /// 查看详情
        /// </summary>
        /// <param name="id"></param>
        /// <param name="assetNo"></param>
        /// <returns></returns>
        public DataTable GetDetail(decimal id, string assetNo)
        {
            var sb = new StringBuilder();
            sb.Append("select t1.ID,t1.AU_Number,t1.AU_Date,t1.AU_Person,t1.AU_Spend,t1.AU_Content,");
            sb.Append("t2.HeadID,t2.AU_AssetNo,t2.AU_AssetName,t2.AU_Specs,t2.AU_Location,t2.WarnTime,");
            sb.Append("t2.StatusTag,t2.Auditor,t2.AuditTime ");
            sb.Append($"from {tableH} t1 left join {tableB} t2 on t1.ID=t2.HeadID ");
            sb.Append("where t1.ID=@id and t2.AU_AssetNo=@assetNo");
            SqlParameter[] param1 = {
                new SqlParameter("@id", id),
                new SqlParameter("@assetNo",assetNo)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), param1);
            return ds.Tables[0];
        }

        /// <summary>
        /// 通过headID,assetNo 查询维护登记子表数据
        /// </summary>
        /// <param name="headID"></param>
        /// <param name="assetNo"></param>
        /// <returns></returns>
        public AssetUpkeepBody GetAssetUpkeepBody(decimal headID, string assetNo)
        {
            var sb = GetSelectBodySql("and HeadID=@headID and AU_AssetNo=@assetNo");
            SqlParameter[] param1 = {
                new SqlParameter("@headID", headID),
                new SqlParameter("@assetNo",assetNo)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), param1);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return CommonOperation.DataTableToModel<AssetUpkeepBody>(ds.Tables[0]);
            }
            else
            {
                return null;
            }

        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool AddAssetUpkeep(AssetUpkeepHead model)
        {
            // 定义主表SQL语句和参数
            StringBuilder hSql = new StringBuilder();
            hSql.Append("insert into " + tableH + " (");
            hSql.Append("AU_Number,AU_Date,AU_Person,AU_Spend,AU_Content)");
            hSql.Append(" OUTPUT inserted.ID values (");
            hSql.Append("@number,@date,@person,@spend,@content)");

            string connectionString = SqlHelper.connectionString;
            Action<SqlConnection, SqlTransaction> sqlAction = (connection, transaction) =>
            {
                using (SqlCommand command1 = new SqlCommand(hSql.ToString(), connection, transaction))
                {
                    command1.CommandType = CommandType.Text;
                    command1.Parameters.AddWithValue("@number", model.AU_Number);
                    command1.Parameters.AddWithValue("@date", model.AU_Date);
                    command1.Parameters.AddWithValue("@person", model.AU_Person);
                    command1.Parameters.AddWithValue("@spend", model.AU_Spend);
                    command1.Parameters.AddWithValue("@content", model.AU_Content);

                    command1.Connection = connection; // 确保连接已设置

                    // 执行命令并获取自增长ID
                    object result = command1.ExecuteScalar();
                    long newID = 0;
                    if (result != null && result is decimal) // 检查是否为null并且能否转换为decimal
                    {
                        newID = Convert.ToInt64(result);
                        model.ID = newID;
                    }
                    //int newID = (int)command1.ExecuteScalar();

                    // 子表数据
                    List<AssetUpkeepBody> itemList = model.ItemList;
                    if (itemList != null && itemList.Count > 0)
                    {
                        SqlCommand command2;

                        StringBuilder itemSql;
                        foreach (AssetUpkeepBody itemModel in itemList)
                        {
                            itemSql = new StringBuilder();
                            itemSql.Append("insert into " + tableB + "(");
                            itemSql.Append("HeadID,AU_AssetNo,AU_AssetName,AU_Specs,AU_Unit,AU_Price,AU_DeptId,AU_DeptName,AU_Location,WarnID,WarnTime)");
                            itemSql.Append(" values (");
                            itemSql.Append("@headID,@assetNo,@assetName,@specs,@unit,@price,@deptId,@deptName,@location,@warnId,@warnTime);");
                            itemSql.Append(" update AssetKeepWarn set Status=0,AU_ID=@auid where ID=@wid;");

                            using (command2 = new SqlCommand(itemSql.ToString(), connection, transaction))
                            {
                                command2.CommandType = CommandType.Text;
                                command2.Parameters.AddWithValue("@headID", newID);
                                command2.Parameters.AddWithValue("@assetNo", itemModel.AU_AssetNo);
                                command2.Parameters.AddWithValue("@assetName", itemModel.AU_AssetName);
                                command2.Parameters.AddWithValue("@specs", itemModel.AU_Specs);
                                command2.Parameters.AddWithValue("@unit", itemModel.AU_Unit);
                                command2.Parameters.AddWithValue("@price", itemModel.AU_Price);
                                command2.Parameters.AddWithValue("@deptId", itemModel.AU_DeptId);
                                command2.Parameters.AddWithValue("@deptName", itemModel.AU_DeptName);
                                command2.Parameters.AddWithValue("@location", itemModel.AU_Location);
                                command2.Parameters.AddWithValue("@warnId", itemModel.WarnID);
                                command2.Parameters.AddWithValue("@warnTime", itemModel.WarnTime);
                                command2.Parameters.AddWithValue("@auid", newID);
                                command2.Parameters.AddWithValue("@wid", itemModel.WarnID);
                                command2.ExecuteNonQuery();
                            }
                        }
                    }
                }
            };

            // 调用DatabaseHelper中的静态方法ExecuteTransaction
            SqlHelper.ExecuteTransaction(sqlAction, connectionString);

            return true;
        }

        ///// <summary>
        ///// 更新一条数据
        ///// </summary>
        //public bool UpdateAssetUpkeep(AssetUpkeepHead model)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("update " + tableH + " set ");
        //    strSql.Append("AU_Date=@date,");
        //    strSql.Append("AU_Person=@person,");
        //    strSql.Append("AU_Spend=@spend,");
        //    strSql.Append("AU_Content=@content");

        //    strSql.Append(" where ID=@id");
        //    SqlParameter[] parameters = {
        //            new SqlParameter("@date", model.AU_Date),
        //            new SqlParameter("@person", model.AU_Person),
        //            new SqlParameter("@spend", model.AU_Spend),
        //            new SqlParameter("@content", model.AU_Content),
        //            new SqlParameter("@id", model.ID)
        //    };
        //    List<CommandInfo> sqllist = new List<CommandInfo>();
        //    CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
        //    sqllist.Add(cmd);

        //    //先删除子表数据
        //    StringBuilder strSql2 = new StringBuilder();
        //    strSql2.Append("delete from " + tableB + " where HeadID=@headID ");
        //    SqlParameter[] parameters2 = { new SqlParameter("@headID", model.ID) };
        //    cmd = new CommandInfo(strSql2.ToString(), parameters2);
        //    sqllist.Add(cmd);

        //    //添加子表数据
        //    if (model.ItemList != null)
        //    {
        //        StringBuilder itemSql;
        //        foreach (AssetUpkeepBody modelt in model.ItemList)
        //        {
        //            itemSql = new StringBuilder();
        //            itemSql.Append("insert into " + tableB + "(");
        //            itemSql.Append("HeadID,AU_AssetNo,AU_AssetName,AU_Specs,AU_Unit,AU_Price,AU_DeptId,AU_DeptName,AU_Location,WarnID)");
        //            itemSql.Append(" values (");
        //            itemSql.Append("@headID,@assetNo,@assetName,@specs,@unit,@price,@deptId,@deptName,@location,@warnId)");
        //            SqlParameter[] parameters3 = {
        //                    new SqlParameter("@headID", model.ID),
        //                    new SqlParameter("@assetNo", modelt.AU_AssetNo),
        //                    new SqlParameter("@assetName", modelt.AU_AssetName),
        //                    new SqlParameter("@specs", modelt.AU_Specs),
        //                    new SqlParameter("@unit", modelt.AU_Unit),
        //                    new SqlParameter("@price", modelt.AU_Price),
        //                    new SqlParameter("@deptId", modelt.AU_DeptId),
        //                    new SqlParameter("@deptName", modelt.AU_DeptName),
        //                    new SqlParameter("@location", modelt.AU_Location),
        //                    new SqlParameter("@warnId", modelt.WarnID)
        //            };
        //            cmd = new CommandInfo(itemSql.ToString(), parameters3);
        //            sqllist.Add(cmd);
        //        }
        //    }
        //    int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
        //    return rowsAffected > 0;
        //}

        /// <summary>
        /// 删除子表数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="assetNo"></param>
        /// <returns></returns>
        public int DeleteBody(decimal id, string assetNo)
        {
            var strSql = new StringBuilder();
            strSql.Append("update " + tableB + " set IsDelete=1");
            strSql.Append(" where HeadID=@id and AU_AssetNo=@assetNo");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id),
                new SqlParameter("assetNo",assetNo)
            };
            int result = SqlHelper.ExecuteSql(strSql.ToString(), parameters);
            return result;
        }

        /// <summary>
        /// 检查子表是否有数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool CheckBody(decimal id)
        {
            var strSql = new StringBuilder();
            strSql.Append($"select count(1) from {tableB}");
            strSql.Append($" where IsDelete=0 and HeadID={id}");
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 删除主表数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteHead(decimal id)
        {
            var strSql = new StringBuilder();
            strSql.Append($"update {tableH} set IsDelete=1");
            strSql.Append(" where ID=@id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            int result = SqlHelper.ExecuteSql(strSql.ToString(), parameters);
            return result;
        }

        /// <summary>
        /// 撤销
        /// </summary>
        public bool Revoke(decimal ID, string assetNo)
        {
            List<CommandInfo> sqllist = new List<CommandInfo>();
            //删除子表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableB + " set IsDelete=1");
            strSql.Append(" where HeadID=@headID");
            SqlParameter[] parameters = { new SqlParameter("@headID", ID) };
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);
            //删除主表
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("update " + tableH + " set IsDelete=1");
            strSql2.Append(" where ID=@id");
            SqlParameter[] parameters2 = { new SqlParameter("@id", ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateStatus(decimal id, int status)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append($"Update {tableH} SET ");
            sb.Append("AU_Status=@status ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@status",status),
                new SqlParameter("@id",id),
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result;
        }
        /// <summary>
        /// 修改子表状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateStatusTag(decimal id, int status, string userName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append($"Update {tableB} SET ");
            sb.Append("StatusTag=@status,");
            sb.Append("Auditor=@auditor,");
            sb.Append("AuditTime=GetDate() ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@status",status),
                new SqlParameter("@auditor",userName),
                new SqlParameter("@id",id)
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result;
        }


    }
}
